package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;


public class DBCustomer implements IFDBCus{
    private  Connection con;
    
    public DBCustomer() {
      con = DBConnection.getInstance().getDBcon();
    }
  
    
    public ArrayList<Customer> getAllCustomer(boolean retriveAssociation)
    {
        ArrayList<Customer> list = new ArrayList<Customer>();
        return list;
    }
    
    public Customer findCustomer(int customerID, boolean retriveAssociation)
    {   String wClause = "  customerID = '" + customerID + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    
  
    public Customer searchCustomername(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public Customer searchCustomerCvr(String attValue, boolean retriveAssociation)
    {
        String wClause = "cvr like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
     
    
    public Customer searchCustomerID(int attValue, boolean retriveAssociation)
    {
        String wClause = "CustomerID = '" + attValue + "'";
        System.out.println("Search Customer: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
 
    @SuppressWarnings("empty-statement")
    public int insertCustomer(Customer cus)
    {  
//       int nextID = GetMax.getMaxId("Select max(ID) from Customer");
//       nextID = nextID + 1;
//       System.out.println("next ID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO Customer(customerID, name, address, zipCode, city, phoneNo, cvr)  VALUES('"+
	  		cus.getCustomerID()  + "','"  +
                   cus.getName()  + "','"  +
                   cus.getAddress()  + "','"  +
                   cus.getZipCode()  + "','"  +
                   cus.getCity()  + "','"  +
                   cus.getPhoneNo()  + "','"  +
                   cus.getCvr()  + "','"  +
//                        nextID + "','" +
                       "')";
                     
  
	   return(rc);
	}

	public int updateCustomer(Customer cus)
	{
		Customer cusObj  = cus;
		int rc=-1;

		String query="UPDATE Customer SET "+
		 	  "name ='"+ cusObj.getName()+"', "+
                          "address ='"+ cusObj.getAddress() + "', " +
                          "zipCode ='"+ cusObj.getZipCode() + "' " +
                        "city ='"+ cusObj.getCity() + "' " +
                        "phoneNo ='"+ cusObj.getPhoneNo() + "' " +
                        "cvr ='"+ cusObj.getCvr() + "' " +
		          " WHERE customerID = '"+ cusObj.getCustomerID() + "'";
                System.out.println("Update query:" + query);
  		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteCustomer(int customerID)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM customer WHERE customerID = '" +
				customerID + "'";
                System.out.println(query);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
        public ArrayList<Customer> searchCustomerNames(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        return miscWhere(wClause, retriveAssociation);
    }
	
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery(wClause);
  
            try{ 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		int snr=0;
		while( results.next() ){
	     	 Customer cusObj = new Customer();
		 cusObj = buildCustomer(results);	
                 list.add(cusObj);	
		}
                 stmt.close();     
                 
			
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			
	 	}
		return list;
	}
	
	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer cusObj = new Customer();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            cusObj = buildCustomer(results);
                           
                            stmt.close();
                            
			}
                        else{
                            cusObj = null;
                        }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cusObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT id, name, address, zipCode, city, phoneNo, cvr  FROM customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	private Customer buildCustomer(ResultSet results)
      {   Customer cusObj = new Customer();
          try{ 
                cusObj.setCustomerID(results.getInt("ID"));
                cusObj.setName(results.getString("name"));
                cusObj.setAddress(results.getString("address"));
	  	cusObj.setZipCode(results.getInt("zipCode"));
	  	cusObj.setCity(results.getString("city"));
		cusObj.setPhoneNo(results.getInt("phoneNo"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return cusObj;
      }
}


    

